﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;



public partial class Import : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e) //Button1_Click函数响应事件，从数据表中导出数据，表名从文本框TextBox1中获得
    {
        DataTableToExcel dt = new DataTableToExcel();
        string tableName = TextBox1.Text;
        /*缺少错误处理，表名不存在的情况*/
        /*此处以STU_INFO表明为例，尚未处理导出数据0712617到excel中没有‘0’的问题*/
        string str_sql = "select * from "+tableName;///////////////////////////////////////////////////////////
        string column_names = "id,name,gender,collegeid,speciality,class,phone,email";  //"id,name,tel"是被分解的字符串，用于插入到excel表格中，需要自定义////////////////////////////
        DataTableToExcel.DataToExcel(dt.GetCompanyAll(str_sql), Page.Response, column_names); // Session["Field_Desc"].ToString().Trim()
    }

    protected void btnExport_Click(object sender, EventArgs e)//丛excel导入到数据库中
    {
        string filepath = this.fileUpload.PostedFile.FileName;
        ExcelToSQL ets = new ExcelToSQL();
        /*该部分获取的是文件名，没有路径，所以很有可能找不到该excel文件，所以如果有问题便可以这样写：   
        string fileName = fileUpload.PostedFile.FileName;          
         fileUpload.PostedFile.SaveAs(Server.MapPath("upfile") + "\\" + fileName);          
        string filepath = Server.MapPath("upfile") + "\\" + fileName; */         
        if (filepath != "")
        {
            if (this.txtTableName.Text != "")
            {
                if (filepath.Contains("xls"))
                {
                    int result = ets.ImportSql(filepath, this.txtTableName.Text); //this.txtTableName.Text获得数据库表名，如"student"
                    if (result == (int)ExcelToSQL.ImportState.tableNameError)
                        ets.Alert("此表名在数据中不存在！");
                    else if (result == (int)ExcelToSQL.ImportState.excelFormatError)
                        ets.Alert("Excel格式不能正确读取！");
                    else if (result == (int)ExcelToSQL.ImportState.fieldMatchError)
                        ets.Alert("Excel里的字段和Sql Server里的字段不匹配！");
                    else if (result == (int)ExcelToSQL.ImportState.dataTypeError)
                        ets.Alert("转换数据类型时发生错误!");
                    else if (result == (int)ExcelToSQL.ImportState.right)
                    {
                        ets.Alert("导入成功");
                    }
                }
                else ets.Alert("上传的文件类型必须为excel文件！");
            }
            else ets.Alert("表名不能为空！");
        }
        else ets.Alert("没有选择要上传的文件！");
    }
}





